#!/bin/bash
source /etc/profile
if [ $# -eq 1 ]; then
	targetHour=$1
else
	targetHour=`date -d "-1 hours" +"%Y%m%d%H"`
fi
path=$(cd $(dirname $0);pwd)
parentPath=$(dirname ${path})
cd ${parentPath}
targetDate=${targetHour:0:8}
targetAnalysisHour=${targetHour:8:2}
if [ ! -d "${path}/${targetDate}" ];then
	mkdir -p ${path}/${targetDate}
fi
day=$(date -d"${targetDate}" "+%Y-%m-%d")
hive -e "add files ${parentPath}/page.txt ${parentPath}/event.txt ${parentPath}/time.txt ${parentPath}/pagenum.txt;add jar ${parentPath}/lib/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';create temporary function getconversion as 'com.nsw.udf.GetConversion';set mapred.job.name=[NAS][overview][${targetDate}];use formatlog; \
from (select cid,sid,session,min(struct(time,lang,region,city,device_type,device,os,browser,resolution,color,flash,isp,country)) as firstinfo,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv,getconversion(cid,sid,concat_ws(',',collect_set(ep)),concat_ws(',',collect_list(cast(eventid as string))),(max(time)-min(time))/1000+60) as conversions from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_overview_result' select a.cid,a.sid,${targetDate},sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_language_result' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col2),sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col2) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_region' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col3),1,sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col3) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_china' select a.cid,a.sid,${targetDate},'全国',0,sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) where a.firstinfo.col13='中国' group by a.cid,a.sid \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_city' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col4),2,sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col4) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_device_type' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col5),case format2other(a.firstinfo.col5) when 'Tablet' then '3' when 'Desktop' then '1' when 'Mobile' then '2' else '0' end,'deviceType',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col5) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_device_model' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col6),case max(format2other(a.firstinfo.col5)) when 'Tablet' then '3' when 'Desktop' then '1' when 'Mobile' then '2' else '0' end,'device',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col6) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_os' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col7),0,'os',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col7) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_browser' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col8),0,'browser',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col8) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_resolution' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col9),0,'resolution',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col9) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_color' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col10),0,'color',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col10) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_flash' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col11),0,'flash',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col11) \
insert overwrite local directory '${path}/${targetDate}/${targetHour}_isp' select a.cid,a.sid,${targetDate},format2other(a.firstinfo.col12),0,'isp',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session),sum(a.conversions) group by a.cid,a.sid,format2other(a.firstinfo.col12)"

# 小时计算
hive -e "set mapred.job.name=[NAS][overview][hour][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_overview_hour_result' select a.cid,a.sid,${targetDate},sum(a.pv),count(distinct a.nasid),count(distinct a.ip),sum(a.visit_time)/sum(a.pv),${targetAnalysisHour} from (select cid,sid,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} and hour=${targetAnalysisHour} and length(cid)>0 and length(sid)>0 group by cid,sid,session) a group by a.cid,a.sid"

#网站概况
#hive -e "set mapred.job.name=[NAS][overview][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_overview_result' select a.cid,a.sid,${targetDate},sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid"
#语言分析
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][visitors][language][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_language_result' select a.cid,a.sid,${targetDate},a.lang,sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,lang)).col2) as lang,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.lang"
#地域分析
#省
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][visitors][region][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_region' select a.cid,a.sid,${targetDate},a.region,1,sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,session,format2other(min(struct(time,region)).col2) as region,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.region"
#全国
#hive -e "set mapred.job.name=[NAS][visitors][country][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_china' select a.cid,a.sid,${targetDate},'全国',0,sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid"
#城市
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][visitors][city][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_city' select a.cid,a.sid,${targetDate},a.city,2,sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,city)).col2) as city,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.city"
#终端分析
#设备类型 pc、平板、手机
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][device][device][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_device_type' select a.cid,a.sid,${targetDate},a.device_type,'device',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,device_type)).col2) as device_type,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.device_type"

#设备型号
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][device][device][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_device_model' select a.cid,a.sid,${targetDate},a.device,'device',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,device)).col2) as device,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.device"
#操作系统
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][device][os][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_os' select a.cid,a.sid,${targetDate},a.os,'os',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,os)).col2) as os,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.os"
#浏览器
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][device][browser][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_browser' select a.cid,a.sid,${targetDate},a.browser,'browser',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,browser)).col2) as browser,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.browser"
#屏幕分辨率
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][device][resolution][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_resolution' select a.cid,a.sid,${targetDate},a.resolution,'resolution',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,resolution)).col2) as resolution,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.resolution"
#屏幕颜色
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][device][color][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_color' select a.cid,a.sid,${targetDate},a.color,'color',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,color)).col2) as color,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.color"
#插件版本
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][device][flash][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_flash' select a.cid,a.sid,${targetDate},a.flash,'flash',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,flash)).col2) as flash,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.flash"
#网络接入商
#hive -e "add jar ${parentPath}/NASUDF-0.0.1.jar;create temporary function format2other as 'com.nsw.udf.Format2Other';set mapred.job.name=[NAS][device][isp][${targetDate}];use formatlog;insert overwrite local directory '${path}/${targetDate}/${targetHour}_isp' select a.cid,a.sid,${targetDate},a.isp,'isp',sum(a.pv),count(distinct a.nasid),count(distinct ip),sum(if(a.pv=1,1,0)) as bounce,sum(a.visit_time)/sum(a.pv) as avg_visittime,count(distinct a.session) from (select cid,sid,format2other(min(struct(time,isp)).col2) as isp,session,max(nasid) as nasid,max(ip) as ip,((max(time)-min(time))/1000+60) as visit_time,count(1) as pv from nas_tracking_format_hour where day=${targetDate} group by cid,sid,session) a group by a.cid,a.sid,a.isp"
wait
#合并地域数据
cat ${path}/${targetDate}/${targetHour}_region/* ${path}/${targetDate}/${targetHour}_china/* ${path}/${targetDate}/${targetHour}_city/* > ${path}/${targetDate}/${targetHour}_area
#合并设备数据
cat ${path}/${targetDate}/${targetHour}_device_type/* ${path}/${targetDate}/${targetHour}_device_model/* ${path}/${targetDate}/${targetHour}_os/* ${path}/${targetDate}/${targetHour}_browser/* ${path}/${targetDate}/${targetHour}_resolution/* ${path}/${targetDate}/${targetHour}_color/* ${path}/${targetDate}/${targetHour}_flash/* ${path}/${targetDate}/${targetHour}_isp/* > ${path}/${targetDate}/${targetHour}_device
#合并概览数据
cat ${path}/${targetDate}/${targetHour}_overview_result/* > ${path}/${targetDate}/${targetHour}_overview
#合并语言数据
cat ${path}/${targetDate}/${targetHour}_language_result/* > ${path}/${targetDate}/${targetHour}_language
#合并小时概览数据
cat ${path}/${targetDate}/${targetHour}_overview_hour_result/* > ${path}/${targetDate}/${targetHour}_overview_hour
wait
# load data to mysql
source ${parentPath}/configs/mysql_config
#overview
python ${path}/load_visitor_overview_to_mysql.py ${path}/${targetDate}/${targetHour}_overview ${host} ${port} ${user} ${password} ${db} ${day}
# nas_visitor_language
python ${path}/load_visitor_language_to_mysql.py ${path}/${targetDate}/${targetHour}_language ${host} ${port} ${user} ${password} ${db} ${day}
# nas_visitor_area
python ${path}/load_visitor_area_to_mysql.py ${path}/${targetDate}/${targetHour}_area ${host} ${port} ${user} ${password} ${db} ${day}
# nas_visitor_device
python ${path}/load_visitor_device_to_mysql.py ${path}/${targetDate}/${targetHour}_device ${host} ${port} ${user} ${password} ${db} ${day}

#nas_visitor_overview_hour
python ${path}/load_visitor_overview_hour_to_mysql.py ${path}/${targetDate}/${targetHour}_overview_hour ${host} ${port} ${user} ${password} ${db} ${day} ${targetAnalysisHour}